package com.swcw.platform.constants;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletResponse;

import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 实现写excel文件功能
 * 
 * @author xulin
 * 
 */
@SuppressWarnings("rawtypes")
public class ExcelUtil {
	private ExcelUtil() {
	}

	/**
	 * 设置头信息
	 * 
	 * @param response
	 *            HttpServletResponse
	 * @param fileName
	 *            默认的文件名称
	 */
	public static void setExcelContentType(HttpServletResponse response,
			String fileName) {
		try {
			fileName = new String(fileName.getBytes("gb2312"), "ISO8859-1");
		} catch (UnsupportedEncodingException e) {
			// should no happen
		}
		response.reset();
		response.setContentType("application/msexcel;charset=utf-8");
		response.setHeader("Content-disposition", "attachment;filename= "
				+ fileName);
	}

	/**
	 * 获取zip压缩流
	 * 
	 * @param response
	 * @return
	 */
	public static ZipOutputStream getZipOutputStream(
			HttpServletResponse response) {
		ZipOutputStream zos = null;
		OutputStream outputStream = null;
		try {
			outputStream = response.getOutputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		zos = new ZipOutputStream(outputStream);
		return zos;

	}

	/**
	 * 关闭流
	 * 
	 * @param outputStream
	 */
	public static void closeStream(OutputStream outputStream) {
		if (outputStream != null) {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

//	/**
//	 * excel根据对象导出数据
//	 * 
//	 * @param response
//	 * @param objects
//	 * @param clz
//	 * @param fieldStr
//	 */
//	public static void write(HttpServletResponse response,
//			List<Object> objects, Class clz, List<KeyValue> keyValueList) {
//		setExcelContentType(response, getFileName());
//		ExportConfigHelper helper = new ExportConfigHelper();
//
//		// 根据properties文件获取需要导出的字段名以及在excel中的标题名称
//		List<FieldTitle> result = helper.getFieldTitles(clz, keyValueList);
//
//		WritableWorkbook wwbook = null;
//		OutputStream os = null;
//		try {
//			os = response.getOutputStream();
//			wwbook = jxl.Workbook.createWorkbook(os);
//			WritableSheet wsheet = wwbook.createSheet("sheet1", 0);// set sheet
//			for (int i = 0; i < result.size(); i++) { // set header title
//				jxl.write.Label titleCell = new jxl.write.Label(i, 0, result
//						.get(i).getTitle());
//				wsheet.addCell(titleCell);
//			}
//			for (int i = 1; i <= objects.size(); i++) { // set value
//				Object obj = objects.get(i - 1);
//				for (int j = 0; j < result.size(); j++) {
//					result.get(j).getField().setAccessible(true);
//					Object value = result.get(j).getField().get(obj);
//					jxl.write.Label valueCell = new jxl.write.Label(j, i,
//							(value != null) ? value.toString() : "");
//					wsheet.addCell(valueCell);
//				}
//			}
//			wwbook.write();
//		} catch (IOException e) {
//			e.printStackTrace();
//		} catch (RowsExceededException e) {
//			e.printStackTrace();
//		} catch (WriteException e) {
//			e.printStackTrace();
//		} catch (IllegalArgumentException e) {
//			e.printStackTrace();
//		} catch (IllegalAccessException e) {
//			e.printStackTrace();
//		} finally {
//			try {
//				wwbook.close();
//				os.write(new byte[]{(byte)0xEF, (byte)0xBB, (byte)0xBF});//utf-8 bom   
//				os.close();  
//
//			} catch (IOException ie) {
//				ie.printStackTrace();
//			} catch (WriteException e) {
//				e.printStackTrace();
//			}
//		}
//	}

//	/**
//	 * excel根据对象导出数据（该功能为在导出多个excel的时候进行压缩）
//	 * 
//	 * @param response
//	 * @param objects
//	 * @param clz
//	 * @param fieldStr
//	 */
//	
//	public static void write(HttpServletResponse response, ZipOutputStream zos,
//			List<Object> objects, Class clz, List<KeyValue> keyValueList) {
//		setExcelContentType(response, getFileName());
//		ExportConfigHelper helper = new ExportConfigHelper();
//
//		setExcelContentType(response, getFileName());
//		ZipEntry ze = null;
//		byte[] buf = new byte[2048]; // 输出文件用的字节数组,每次发送2048个字节到输出流：
//		int readLength = 0;
//
//		// 根据properties文件获取需要导出的字段名以及在excel中的标题名称
//		List<FieldTitle> result = helper.getFieldTitles(clz, keyValueList);
//
//		WritableWorkbook wwbook = null;
//		OutputStream os = null;
//		try {
//			os = response.getOutputStream();
//			wwbook = jxl.Workbook.createWorkbook(os);
//			WritableSheet wsheet = wwbook.createSheet("sheet1", 0);// set sheet
//			for (int i = 0; i < result.size(); i++) { // set header title
//				jxl.write.Label titleCell = new jxl.write.Label(i, 0, result
//						.get(i).getTitle());
//				wsheet.addCell(titleCell);
//			}
//			for (int i = 1; i <= objects.size(); i++) { // set value
//				Object obj = objects.get(i - 1);
//				for (int j = 0; j < result.size(); j++) {
//					result.get(j).getField().setAccessible(true);
//					Object value = result.get(j).getField().get(obj);
//					jxl.write.Label valueCell = new jxl.write.Label(j, i,
//							(value != null) ? value.toString() : "");
//					wsheet.addCell(valueCell);
//				}
//			}
//
//			wwbook.write();
//			wwbook.close();
//			File f = new File(getFileName());
//			ze = new ZipEntry(getFileName());
//			ze.setSize(f.length());
//			ze.setTime(f.lastModified());
//			zos.putNextEntry(ze);
//			InputStream is = new BufferedInputStream(new FileInputStream(f));
//			while ((readLength = is.read(buf, 0, 2048)) != -1) {
//				zos.write(buf, 0, readLength);
//			}
//			is.close();
//		} catch (IOException e) {
//			e.printStackTrace();
//		} catch (RowsExceededException e) {
//			e.printStackTrace();
//		} catch (WriteException e) {
//			e.printStackTrace();
//		} catch (IllegalArgumentException e) {
//			e.printStackTrace();
//		} catch (IllegalAccessException e) {
//			e.printStackTrace();
//		} finally {
//			try {
//				wwbook.close();
//				os.close();
//			} catch (IOException ie) {
//				ie.printStackTrace();
//			} catch (WriteException e) {
//				e.printStackTrace();
//			}
//		}
//	}

	/**
	 * excel根据指定的字段导出 (元素集合放入map中去了) listMaps为通过数据库返回过来的数据 keyValueList
	 * 为标题信息和标题代码值（代码值主要根据listMaps字段中的数据做匹配）
	 * 
	 * @param response
	 * @param objects
	 * @param clz
	 * @param fieldStr
	 */

	public static void write(HttpServletResponse response,
			List<Map<String, Object>> listMaps, List<KeyValue> keyValueList,String title,Map hebin) {
		setExcelContentType(response, getFileName(title));
		WritableWorkbook wwbook = null;
		OutputStream os = null;
		try {
			os = response.getOutputStream();
			wwbook = jxl.Workbook.createWorkbook(os);
			WritableSheet wsheet = wwbook.createSheet(title, 0);// set sheet
			for (int i = 0; i < keyValueList.size(); i++) { // set header title
				jxl.write.Label titleCell = new jxl.write.Label(i, 0,
						keyValueList.get(i).getValue());
				wsheet.addCell(titleCell);
			}
			for (int i = 1; i <= listMaps.size(); i++) { // set value
				Map<String, Object> map = listMaps.get(i - 1);
				for (int j = 0; j < keyValueList.size(); j++) {
					KeyValue keyValue = keyValueList.get(j);
					jxl.write.Label valueCell = new jxl.write.Label(j, i, (map
							.get(keyValue.getKey()) != null) ? map.get(
							keyValue.getKey()).toString() : "");
					wsheet.addCell(valueCell);
					
				}
			}
			if(hebin.size()>0){
				Integer xiacichushiNum=3;
				for (Object m:hebin.keySet()) {
					wsheet.mergeCells(1,xiacichushiNum,1,xiacichushiNum+Integer.parseInt(String.valueOf(hebin.get(m)))-1);
					xiacichushiNum=xiacichushiNum+Integer.parseInt(String.valueOf(hebin.get(m)));
				}
				Integer xiacichushiNum2=3;
				for (Object m:hebin.keySet()) {
					wsheet.mergeCells(10,xiacichushiNum2,10,xiacichushiNum2+Integer.parseInt(String.valueOf(hebin.get(m)))-1);
					xiacichushiNum2=xiacichushiNum2+Integer.parseInt(String.valueOf(hebin.get(m)));
				}
			}
			wwbook.write();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} finally {
			try {
				wwbook.close();
				os.close();
			} catch (IOException ie) {
				System.out.println(ie.getMessage());
			} catch (WriteException e) {
				System.out.println(e.getMessage());
			}
		}
	}
	

	/**
	 * 多sheet
	 * excel根据指定的字段导出 (元素集合放入map中去了) listMaps为通过数据库返回过来的数据 keyValueList
	 * 为标题信息和标题代码值（代码值主要根据listMaps字段中的数据做匹配）
	 * 
	 * @param response
	 * @param objects
	 * @param clz
	 * @param fieldStr
	 */

	public static void write(HttpServletResponse response,
			List<List<Map<String, Object>>> listMapss, List<List<KeyValue>> keyValueLists,String[] stitle,String title) {
		setExcelContentType(response, getFileName(title));
		WritableWorkbook wwbook = null;
		OutputStream os = null;
		try {
			os = response.getOutputStream();
			wwbook = jxl.Workbook.createWorkbook(os);
			for (int k = 0; k < listMapss.size(); k++) {
				List<KeyValue> keyValueList = keyValueLists.get(k);
				List<Map<String, Object>> listMaps = listMapss.get(k);
				WritableSheet wsheet = wwbook.createSheet(stitle[k], k);// set sheet
				for (int i = 0; i < keyValueList.size(); i++) { // set header title
					jxl.write.Label titleCell = new jxl.write.Label(i, 0,
							keyValueList.get(i).getValue());
					wsheet.addCell(titleCell);
				}
				for (int i = 1; i <= listMaps.size(); i++) { // set value
					Map<String, Object> map = listMaps.get(i - 1);
					for (int j = 0; j < keyValueList.size(); j++) {
						KeyValue keyValue = keyValueList.get(j);
						jxl.write.Label valueCell = new jxl.write.Label(j, i, (map
								.get(keyValue.getKey()) != null) ? map.get(
								keyValue.getKey()).toString() : "");
						wsheet.addCell(valueCell);
						
					}
				}
			}
			wwbook.write();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} finally {
			try {
				wwbook.close();
				os.close();
			} catch (IOException ie) {
				System.out.println(ie.getMessage());
			} catch (WriteException e) {
				System.out.println(e.getMessage());
			}
		}
	}


	/**
	 * excel导出成压缩包 excel根据指定的字段导出 (元素集合放入map中去了) listMaps为通过数据库返回过来的数据
	 * keyValueList 为标题信息和标题代码值（代码值主要根据listMaps字段中的数据做匹配）
	 * 
	 * @param response
	 * @param objects
	 * @param clz
	 * @param fieldStr
	 */

	public static File writeToFile(HttpServletResponse response,
			List<Map<String, Object>> listMaps, List<KeyValue> keyValueList,
			String index) {
		String xlsFileName = getExcelName(index);
		WritableWorkbook wwbook = null;
		File file = new File(xlsFileName);
		try {
			wwbook = jxl.Workbook.createWorkbook(file);
			WritableSheet wsheet = wwbook.createSheet("sheet1", 0);// set sheet
			for (int i = 0; i < keyValueList.size(); i++) { // set header title
				jxl.write.Label titleCell = new jxl.write.Label(i, 0,
						keyValueList.get(i).getValue());
				wsheet.addCell(titleCell);
			}

			for (int i = 1; i <= listMaps.size(); i++) { // set value
				Map<String, Object> map = listMaps.get(i - 1);
				for (int j = 0; j < keyValueList.size(); j++) {
					KeyValue keyValue = keyValueList.get(j);
					jxl.write.Label valueCell = new jxl.write.Label(j, i, (map
							.get(keyValue.getKey()) != null) ? map.get(
							keyValue.getKey()).toString() : "");
					wsheet.addCell(valueCell);
				}
			}
			wwbook.write();
			wwbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return file;
	}

//	/**
//	 * 重载方法
//	 * 
//	 * @param response
//	 * @param listMaps
//	 * @param keyValueList
//	 * @param index
//	 * @return
//	 */
//	public static File writeToFile(HttpServletResponse response,
//			Pagination pagination, List<KeyValue> keyValueList, String index) {
//		// 获得总的记录数
//		pagination.getTotalCount();
//		String xlsFileName = getExcelName(index);
//		WritableWorkbook wwbook = null;
//		File file = new File(xlsFileName);
//		try {
//			wwbook = jxl.Workbook.createWorkbook(file);
//			WritableSheet wsheet = wwbook.createSheet("sheet1", 0);// set sheet
//			for (int i = 0; i < keyValueList.size(); i++) { // set header title
//				jxl.write.Label titleCell = new jxl.write.Label(i, 0,
//						keyValueList.get(i).getValue());
//				wsheet.addCell(titleCell);
//			}
//
//			// for (int i = 1; i <= listMaps.size(); i++) { // set value
//			// Map<String,Object> map = listMaps.get(i - 1);
//			// for(int j=0;j<keyValueList.size();j++)
//			// {
//			// KeyValue keyValue=keyValueList.get(j);
//			// jxl.write.Label valueCell = new jxl.write.Label(j, i,
//			// (map.get(keyValue.getKey()) != null) ?
//			// map.get(keyValue.getKey()).toString() : "");
//			// wsheet.addCell(valueCell);
//			// }
//			// }
//			for (int i = 1; i <= pagination.getTotalCount(); i++) {
//				// Map<String, Object> map = listMaps.get(i - 1);
//				 Map<String, Object> map = (Map<String, Object>) pagination.list.get(i-1) ;
//				for (int j = 0; j < keyValueList.size(); j++) {
//					KeyValue keyValue = keyValueList.get(j);
//					jxl.write.Label valueCell = new jxl.write.Label(j, i, (map
//							.get(keyValue.getKey()) != null) ? map.get(
//							keyValue.getKey()).toString() : "");
//					wsheet.addCell(valueCell);
//				}
//			}
//			wwbook.write();
//			wwbook.close();
//		} catch (IOException e) {
//			e.printStackTrace();
//		} catch (RowsExceededException e) {
//			e.printStackTrace();
//		} catch (WriteException e) {
//			e.printStackTrace();
//		} catch (IllegalArgumentException e) {
//			e.printStackTrace();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return file;
//	}

	public static String getFileName(String title) {
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm");
		StringBuilder sb = new StringBuilder();
		sb.append(title+sf.format(System.currentTimeMillis()));
		sb.append(".xls");
		return sb.toString();
	}
	
	public static String getFileName() {
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm");
		StringBuilder sb = new StringBuilder();
		sb.append("直供一线导出文档"+sf.format(System.currentTimeMillis()));
		sb.append(".xls");
		return sb.toString();
	}

	public static String getRandom() {
		Random r = new Random(); // 实例化一个Random类
		StringBuilder sb = new StringBuilder();
		sb.append(r.nextInt(100000));
		sb.append(".xls");
		return sb.toString();
	}

	public static String getExcelName(String name) {
		@SuppressWarnings("unused")
		Random r = new Random(); // 实例化一个Random类
		StringBuilder sb = new StringBuilder();
		sb.append(name);
		sb.append(".xls");
		return sb.toString();
	}
}
